from data_access.read_db import execute_sql, execute_select
from data_access.db_conn import engine
import pandas as pd
from sqlalchemy import create_engine, MetaData, Table, Column, String, Float, Integer

# 读取CSV文件
csv_file_path = '../../data-source/Data-Copilot/Data-Copilot/Educations/P1-TakeupRate.csv'


df = pd.read_csv(csv_file_path)

# 定义数据库表的名称
table_name = 'education_takeup_rate'

# 创建表结构（如果尚不存在）
metadata = MetaData()
table = Table(
    table_name, metadata,
    Column('school_name', String(255)),
    Column('phase_1', Integer()),
    Column('2A', Integer()),
    Column('2B', Integer()),
    Column('2C', Integer()),
    Column('2C_S', Integer()),
    Column('total_vacancy', Integer()),

)

# 创建表（如果不存在）
metadata.create_all(engine)


# 准备插入数据的SQL语句
insert_query = f"""
INSERT INTO {table_name} (
    school_name, phase_1, 2A, 2B, 2C, 2C_S, total_vacancy
) VALUES ("%s", %s, %s, %s, %s, %s, %s)
"""

# 插入数据
for index, row in df.iterrows():
    sql = insert_query % (
        row['School'], row['Phase 1'], row['2A'], row['2B'],
        row['2C'], row['2C(S)'], row['Total Vacancy'],

    )
    execute_sql(sql)

print("Data has been written to the database.")

